#!/usr/bin/env python
# xml tree <--> sqltree converter

# $Id$
"""
Utilities for module main.py 

"""

import psycopg

import xmlsql
#from main_pack import pack_utils , pack
from sql import sql_quote
from table import auto_row

def show_sizes ( options ) :
    cr = options.conn.cursor()
    cr.execute ( 'set search_path to '+options.schema )
    print '  xml table      records '
    print '--------------- ---------'
    cr.execute ( '\nunion\n'.join(
        [ "select %d , '%s' as name , %s as total from %s" % (
        n,t,(t.endswith('_seq') and 'last_value' or 'count(*)'),t)
          for n,t in enumerate(
        ('name_id_seq','name','value_id_seq','value',
         'attr_pair_seq','attr_pair','attr_list_seq','attr_list',
         'fragment_seq','fragment_forest','map_seq','map',
         'document_seq','document'))
        ])+'\norder by 1\n' )
    for row in cr.fetchall() : print '%-15s %9d' % (row[1],row[2])
    print '  user table     records '
    print '--------------- ---------'
    tables = filtertables ( options.tables , options.conn , options.schema )
    if tables :
        cr.execute ( '\nunion\n'.join(
            [ "select %d , '%s' as name , count(*) as total from %s" %
              (n,t.table_name,t.table_name)
              for n,t in enumerate(tables)])+'\norder by 1\n' )
        for row in cr.fetchall() : print '%-15s %9d' % (row[1],row[2])
    print '=============== ========='
    
def autocommit ( conn , request , *extra ) :
    """Execute request and commit"""
    cr = conn.cursor()
    cr.execute ( request , *extra )
    conn.commit()

def autorollback ( conn , request , *extra ) :
    """Execute request on connection. If Ok -> commit else ->rollback"""
    try: autocommit ( conn , request , *extra )
    except :
        conn.rollback()
        return 0
    return 1

def connect ( options ) :
    options.conn = psycopg.connect ( options.dsn )
    cr = options.conn.cursor()
    cr.execute("set client_encoding to 'utf-8'")

def disconnect ( options ) :
    options.conn.commit()
    options.conn.close()
    delattr ( options , 'conn' )
    
class _translator :
    def __init__ ( self , dict_ ) : self.dict = dict_
    def __call__ ( self , arg ) : return self.dict.get(arg,arg)
    
def make_gate ( options ) :
    xml2sqltags = options.tags and _translator(dict(options.tags))
    xml2sqlattrs = options.attributes and _translator(dict(options.attributes))
    sql2xmltags = options.tags and _translator(
        dict([(y,x) for x,y in options.tags]))
    sql2xmlattrs = options.attributes and _translator(
        dict([(y,x) for x,y in options.attributes]))
    
    options.tables = filtertables(options.tables,options.conn,options.schema)
        
    options.gate = xmlsql.XmlSql (
        options.conn ,
        schema=options.schema , tables = options.tables ,
        xml2sqltags = xml2sqltags , xml2sqlattrs = xml2sqlattrs ,
        sql2xmltags = sql2xmltags , sql2xmlattrs = sql2xmlattrs )
    
def filtertables ( tables , conn , schema ) :
    candidates = get_candidates ( conn , schema )
    #print candidates
    if tables is None : tables = candidates
    else : tables = filter(lambda x : x in candidates,tables)
    return [ auto_row(t,conn,schema) for t in tables ]

def get_candidates ( conn , schema ) :
    cr = conn.cursor()
    cr.execute ( """
    select distinct table_name
    from information_schema.columns
    where table_schema = '%s' and column_name = 'attr_list'
    and data_type='bigint'
    and table_name not in ('attr_list','fragment_forest')
    """ % sql_quote(schema) )
    return [r[0] for r in cr.fetchall()]
#===========================================================
